package edu.buaa.resourceManager.store;

import edu.buaa.resourceManager.vo.CustomProperty;
import edu.buaa.resourceManager.vo.FileEntity;
import edu.buaa.resourceManager.vo.ResourcePath;

import java.sql.*;
import java.util.List;

/**
 * Created by song on 17-6-21.
 */
public class SQLiteDAO
{
    private final Connection con;

    public SQLiteDAO(Connection connection) throws SQLException {
        this.con = connection;
        this.con.setAutoCommit(false);
    }

    public int newFileEntity(FileEntity entity) throws SQLException {
        String sql = "INSERT into file_entity (`size`, sha512, md5_head, create_t, update_t) VALUES(?,?,?,?,?)";

        try (PreparedStatement pstmt = this.con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setLong(1, entity.getSize());
            pstmt.setString(2, entity.getSha512());
            pstmt.setString(3, entity.getHeadMd5());
            pstmt.setDate(4, entity.getCreateTime());
            pstmt.setDate(5, entity.getLastModifiedTime());

            int affectedRows = pstmt.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("Creating file failed, no rows affected.");
            }

            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    entity.setId(generatedKeys.getInt(1));
                    return entity.getId();
                }else {
                    throw new SQLException("Creating file failed, no ID obtained.");
                }
            }
        }
    }

    public int updateFileEntity(FileEntity entity) throws SQLException {
        String update = "UPDATE file_entity SET size=?,sha512=?,md5_head=?,create_t=?,update_t=? WHERE id=?";
        try (PreparedStatement pstmt  = this.con.prepareStatement(update)) {
            pstmt.setLong(1, entity.getSize());
            pstmt.setString(2, entity.getSha512());
            pstmt.setString(3, entity.getHeadMd5());
            pstmt.setDate(4, entity.getCreateTime());
            pstmt.setDate(5, entity.getLastModifiedTime());
            pstmt.setInt(6, entity.getId());
            int affectedRows = pstmt.executeUpdate();
            return affectedRows;
        }
    }

    public int deleteFileEntity(FileEntity entity) throws SQLException {
        String update = "DELETE FROM file_entity WHERE id=?";
        try (PreparedStatement pstmt  = this.con.prepareStatement(update)) {
            pstmt.setInt(1, entity.getId());
            int affectedRows = pstmt.executeUpdate();
            return affectedRows;
        }
    }

    public int newResourceFile(ResourcePath path) throws SQLException {
        String sql;
        boolean hasEntity = (path.getFileEntity()!=null && path.getFileEntity().getId()!=0);
        if(hasEntity){
            sql = "INSERT into res_file (`path`, `online`, meta_update_t, comment, entity_id) VALUES(?,?,?,?,?)";
        }else{
            sql = "INSERT into res_file (`path`, `online`, meta_update_t, comment) VALUES(?,?,?,?)";
        }

        try (PreparedStatement pstmt = this.con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, path.getPath());
            pstmt.setBoolean(2, path.isOnline());
            pstmt.setDate(3, new Date(System.currentTimeMillis()));
            pstmt.setString(4, path.getComment());
            if(hasEntity){
                pstmt.setInt(5, path.getFileEntity().getId());
            }
            int affectedRows = pstmt.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("Creating file failed, no rows affected.");
            }

            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    path.setId(generatedKeys.getInt(1));
                    return path.getId();
                }else {
                    throw new SQLException("Creating file failed, no ID obtained.");
                }
            }
        }
    }

    public int updateResourceFile(ResourcePath resourcePath) throws SQLException {
        String update = "UPDATE res_file SET path=?,online=?,comment=?,meta_update_t=? WHERE id=?";
        try (PreparedStatement pstmt  = this.con.prepareStatement(update)) {
            pstmt.setString(1, resourcePath.getPath());
            pstmt.setBoolean(2, resourcePath.isOnline());
            pstmt.setString(3, resourcePath.getComment());
            pstmt.setDate(4, resourcePath.getMetaUpdateTime());
            pstmt.setInt(5, resourcePath.getId());
            int affectedRows = pstmt.executeUpdate();
            return affectedRows;
        }
    }

    public int deleteResourceFile(ResourcePath resourcePath) throws SQLException {
        String update = "DELETE FROM res_file WHERE id=?";
        try (PreparedStatement pstmt  = this.con.prepareStatement(update)) {
            pstmt.setInt(1, resourcePath.getId());
            int affectedRows = pstmt.executeUpdate();
            return affectedRows;
        }
    }

//    public boolean fetchResourceFilesBySha512(FileEntity entity) throws SQLException {
//        String select = "SELECT * FROM res_file, file_entity WHERE sha512='" + entity.getSha512() + "'";
//
//        ResourcePath file;
//        try (Statement stmt = this.con.createStatement()) {
//            ResultSet rs = stmt.executeQuery(select);
//            if (rs.next()) {
//                file = new ResourcePath(
//                        rs.getInt("id"),
//                        rs.getString("path"),
//                        rs.getBoolean("online"),
//                        rs.getDate("meta_update_t"),
//                        rs.getString("comment")
//                );
//            } else {
//                throw new SQLException("File Not Found in Database.");
//            }
//            rs.close();
//        }
//    }

    private void newCustomProperty(long id, CustomProperty p) throws SQLException {
        String sql2 = "INSERT INTO file_property (file_id, name, value) VALUES(?,?,?)";
        try(PreparedStatement stat = this.con.prepareStatement(sql2)){
            stat.setLong(1, id);
            stat.setString(2, p.getName());
            stat.setString(3, p.getValue());
            stat.executeUpdate();
        }
    }

    private void newCustomProperties(long id, List<CustomProperty> list) throws SQLException {
        String sql2 = "INSERT INTO file_property (file_id, name, value) VALUES(?,?,?)";
        try (PreparedStatement stat = this.con.prepareStatement(sql2)) {
            for(CustomProperty p : list) {
                stat.setLong(1, id);
                stat.setString(2, p.getName());
                stat.setString(3, p.getValue());
                stat.addBatch();
            }
            stat.executeUpdate();
        }
    }

    private void updateCustomProperty(long id, CustomProperty p) throws SQLException {
        String sql2 = "UPDATE file_property set value=? WHERE file_id=? AND name=?";
        try(PreparedStatement stat = this.con.prepareStatement(sql2)){
            stat.setString(1, p.getValue());
            stat.setLong(2, id);
            stat.setString(3, p.getName());
            stat.executeUpdate();
        }
    }

    private void deleteCustomProperty(long id, String key) throws SQLException {
        String sql2 = "DELETE FROM file_property WHERE file_id=? AND name=?";
        try(PreparedStatement stat = this.con.prepareStatement(sql2)){
            stat.setLong(1, id);
            stat.setString(2, key);
            stat.executeUpdate();
        }
    }

    public void newFileRel(ResourcePath resourcePath, ResourcePath f) {

    }
}
